const Router = require('koa-router')
const { sequelize } = require('../database/db')
const { QueryTypes } = require('sequelize')
const Op = require('sequelize').Op
const { OrganVirusData } = require('../database/models/organVirusData')
const { Metadata } = require('../database/models/metadata')
const { MetamapExpression } = require('../database/models/metamapExpression')
// const check = require('../pem/check')

let router = new Router()

router.get('/list', async(ctx) => {
    const data = await sequelize.query(
        `SELECT System, Organ FROM OrganVirusData`,
        { type: QueryTypes.SELECT }
    );
    const systemObj = {}
    const systemList = []
    data.forEach(v => {
        if (systemObj[v.System]) {
            if (systemObj[v.System][v.Organ]) {
                systemObj[v.System][v.Organ].num += 1
            } else {
                systemObj[v.System][v.Organ] = {
                    name: v.Organ,
                    num: 1
                }
            }
        } else {
            systemObj[v.System] = {}
            systemObj[v.System][v.Organ] = {
                name: v.Organ,
                num: 1
            }
        }
    })
    for (const i in systemObj) {
        systemList.push({
            name: i,
            img: i,
            list: list(systemObj[i])
        })
    }
    function list(e) {
        const list = []
        for(const k in e) {
            list.push({
                name: e[k].name,
                num: e[k].num
            })
        }
        return list
    }
    ctx.body = {
        code: 101,
        data: systemList,
        msg: ''
    }
})

router.get('/taxonomy', async(ctx) => {
    const data = await sequelize.query(
        `SELECT NucleicAcidType, Phylum, Class, \`Order\`, Family FROM OrganVirusData`,
        { type: QueryTypes.SELECT }
    );
    const systemObj = {
        NucleicAcidType: {},
        Phylum: {},
        Class: {},
        Order: {},
        Family: {}
    }
    const systemList = []
    data.forEach(v => {
        for (const k in v) {
            if (systemObj[k][v[k]]) {
                systemObj[k][v[k]] += 1
            } else {
                systemObj[k][v[k]] = 1
            }
        }
    })
    for (const i in systemObj) {
        systemList.push({
            name: i,
            list: list(systemObj[i])
        })
    }
    function list(e) {
        const list = []
        for(const k in e) {
            list.push({
                name: k,
                num: e[k]
            })
        }
        return list
    }
    ctx.body = {
        code: 101,
        data: systemList,
        msg: ''
    }
})

router.get('/family', async(ctx) => {
    const data = await sequelize.query(
        `SELECT Family as family,COUNT(*) as num FROM OrganVirusData GROUP BY Family`,
        { type: QueryTypes.SELECT }
    );

    ctx.body = {
        code: 101,
        data,
        msg: ''
    }
})

router.post('/download', async(ctx) => {
    const { type, order, content } = ctx.request.body
    if (type && order && content && content.length) {
        try {
            let data = null
            if (type === 1) {
                data = await OrganVirusData.findAll({
                    where: {
                        [order]: {
                            [Op.or]: content
                        }
                    }
                })
            } else if (type === 2) {
                data = await Metadata.findAll({
                    where: {
                        [order]: {
                            [Op.or]: content
                        }
                    }
                })
            } else {
                data = await MetamapExpression.findAll({
                    where: {
                        [order]: {
                            [Op.or]: content
                        }
                    }
                })
            }
            ctx.body = {
                code: 101,
                data,
                msg: '成功'
            }
        } catch (err) {
            ctx.body = {
                code: 102,
                data: null,
                msg: ''
            }
        }
    } else {
        ctx.body = {
            code: 102,
            data: null,
            msg: ''
        }
    }
})

router.get('/organ', async(ctx) => {
    const data = await sequelize.query(
        `select distinct System, Organ FROM OrganVirusData`,
        { type: QueryTypes.SELECT }
    );
    ctx.body = {
        code: 101,
        data,
        msg: ''
    }
})

router.post('/organInfo', async(ctx) => {
    const { organ } = ctx.request.body
    if (organ) {
        try {
            const data = await OrganVirusData.findAll({
                attributes: ['Species', 'Genus', 'Family', 'VirusTaxonomy'],
                where: {
                    Organ: organ
                }
            });
            ctx.body = {
                code: 101,
                data,
                msg: '成功'
            }
        } catch (err) {
            ctx.body = {
                code: 102,
                data: null,
                msg: ''
            }
        }
    } else {
        ctx.body = {
            code: 102,
            data: null,
            msg: ''
        }
    }
})

router.post('/organViolin', async(ctx) => {
    const { organ } = ctx.request.body
    if (organ) {
        try {
            const data = await MetamapExpression.findAll({
                attributes: ['VirusName', 'Expression'],
                where: {
                    Organ: organ
                }
            });
            ctx.body = {
                code: 101,
                data,
                msg: '成功'
            }
        } catch (err) {
            ctx.body = {
                code: 102,
                data: null,
                msg: ''
            }
        }
    } else {
        ctx.body = {
            code: 102,
            data: null,
            msg: ''
        }
    }
})

router.post('/organVirus', async(ctx) => {
    const { NucleicAcidType, Family, Genus, Species } = ctx.request.body
    try {
        let attributes = []
        let group = []
        let where = {}
        if (NucleicAcidType) {
            attributes = ['Family']
            group = ['Family']
            if (NucleicAcidType === 'Unclassified') {

            } else if (NucleicAcidType === 'Reverse Transcribing') {
                where.NucleicAcidType = 'Reverse-Transcribing'
            } else if (NucleicAcidType === 'All Virus') {
                where.NucleicAcidType = null
            } else {
                where.NucleicAcidType = NucleicAcidType
            }
        }
        if (Family) {
            attributes = ['Genus']
            group = ['Genus']
            if (Family === 'Unassigned') {
                where.Family = null
            } else {
                where.Family = Family
            }
        }
        if (Genus) {
            attributes = ['Species']
            group = ['Species']
            if (Genus === 'Unassigned') {
                where.Genus = null
            } else {
                where.Genus = Genus
            }
        }
        if (Species) {
            let where2 = []
            for (const k in where) {
                if (where[k]) {
                    where2.push(k + "='" + where[k] + "'")
                }
            }
            if (NucleicAcidType === 'Unclassified') {
                where2.push("(NucleicAcidType='Unclassified-RNA' OR NucleicAcidType='Unclassified-DNA')")
            }
            const w = where2.join(' AND ')
            const data = await sequelize.query(
                `SELECT a.VirusName,a.VirusTaxonomy,
                GROUP_CONCAT(DISTINCT a.Organ) Organ,
                GROUP_CONCAT(DISTINCT a.System) System,
                GROUP_CONCAT(b.Expression) Expression 
                FROM OrganVirusData a LEFT JOIN MetamapExpressions b 
                on a.VirusTaxonomy = b.VirusTaxonomy 
                WHERE ${w} 
                GROUP BY VirusTaxonomy`,
                { type: QueryTypes.SELECT }
            );
            ctx.body = {
                code: 101,
                data,
                msg: '成功'
            }
        } else {
            let where2 = {}
            for (const k in where) {
                if (where[k]) {
                    where2[k] = where[k]
                }
            }
            if (NucleicAcidType === 'Unclassified') {
                where2[Op.or] = [{NucleicAcidType: 'Unclassified-RNA'}, {NucleicAcidType: 'Unclassified-DNA'}]
            }
            const data = await OrganVirusData.findAll({
                attributes,
                where: where2,
                group
            });
            if(Species) {
                const virusObj = {}
                const virusList = []
                data.forEach(v => {
                    if (virusObj[v.VirusTaxonomy]) {
                        virusObj[v.VirusTaxonomy].Organ.push(v.Organ)
                    } else {
                        virusObj[v.VirusTaxonomy] = {
                            Organ: [v.Organ],
                            VirusName: v.VirusName,
                            VirusTaxonomy: v.VirusTaxonomy
                        }
                    }
                })
                const key = []
                for (const i in virusObj) {
                    key.push({VirusTaxonomy: i})
                }
                const expData = await MetamapExpression.findAll({
                    attributes: ['VirusTaxonomy', 'Expression'],
                    where: {
                        [Op.or]: key
                    }
                });
                const expDataObj = {}
                expData.forEach(v => {
                    if (expDataObj[v.VirusTaxonomy]) {
                        expDataObj[v.VirusTaxonomy] += v.Expression
                    } else {
                        expDataObj[v.VirusTaxonomy] = v.Expression
                    }
                })
                for (const i in virusObj) {
                    virusObj[i].Organ = virusObj[i].Organ.join(',')
                    virusObj[i].Expression = expDataObj[i]
                    virusList.push(virusObj[i])
                }
                ctx.body = {
                    code: 101,
                    data: virusList,
                    msg: '成功'
                }
            } else {
                ctx.body = {
                    code: 101,
                    data,
                    msg: '成功'
                }
            }
        }
    } catch (err) {
        ctx.body = {
            code: 102,
            data: '服务端错误',
            msg: ''
        }
    }
})

router.post('/organVirus2', async(ctx) => {
    const { NucleicAcidType, Family, Genus, Species } = ctx.request.body
    try {
        let where = {}
        if (NucleicAcidType) {
            if (NucleicAcidType === 'Unclassified') {

            } else if (NucleicAcidType === 'Reverse Transcribing') {
                where.NucleicAcidType = 'Reverse-Transcribing'
            } else if (NucleicAcidType === 'All Virus') {
                where.NucleicAcidType = null
            } else {
                where.NucleicAcidType = NucleicAcidType
            }
        }
        if (Family) {
            if (Family === 'Unassigned') {
                where.Family = null
            } else {
                where.Family = Family
            }
        }
        if (Genus) {
            if (Genus === 'Unassigned') {
                where.Genus = null
            } else {
                where.Genus = Genus
            }
        }
        if (Species) {
            where.Species = Species
            let where2 = []
            for (const k in where) {
                if (where[k]) {
                    where2.push(k + "='" + where[k] + "'")
                }
            }
            if (NucleicAcidType === 'Unclassified') {
                where2.push("(NucleicAcidType='Unclassified-RNA' OR NucleicAcidType='Unclassified-DNA')")
            }
            const w = where2.join(' AND ')
            const list = await sequelize.query(
                `SELECT a.VirusName,a.VirusTaxonomy,a.System,a.Organ,a.Evidence,
                 GROUP_CONCAT(b.Expression) Expression 
                 FROM OrganVirusData a LEFT JOIN MetamapExpressions b 
                 on (a.VirusTaxonomy = b.VirusTaxonomy AND a.Organ = b.Organ)
                 WHERE ${w}
                 GROUP BY VirusTaxonomy,Organ`,
                { type: QueryTypes.SELECT }
            );
            const meta = await sequelize.query(
                `SELECT COUNT(*) count FROM Metadata WHERE VirusTaxonomyID = '${list.length ? list[0].VirusTaxonomy : ''}'`,
                { type: QueryTypes.SELECT }
            );
            ctx.body = {
                code: 101,
                data: {
                    list,
                    count: meta.length ? meta[0].count : 0
                },
                msg: '成功'
            }
        }
    } catch (err) {
        ctx.body = {
            code: 102,
            data: '服务端错误',
            msg: ''
        }
    }
})

router.post('/organVirusInfo', async(ctx) => {
    const { organ, id } = ctx.request.body
    if (organ && id) {
        try {
            const data = await OrganVirusData.findAll({
                where: {
                    Organ: organ,
                    VirusTaxonomy: id
                }
            });
            ctx.body = {
                code: 101,
                data,
                msg: '成功'
            }
        } catch (err) {
            ctx.body = {
                code: 102,
                data: null,
                msg: ''
            }
        }
    } else {
        ctx.body = {
            code: 102,
            data: null,
            msg: ''
        }
    }
})

router.post('/organVirusFamily', async(ctx) => {
    const { family } = ctx.request.body
    if (family) {
        try {
            const data = await OrganVirusData.findAll({
                where: {
                    Family: family
                },
                group: ['Family']
            });
            ctx.body = {
                code: 101,
                data,
                msg: '成功'
            }
        } catch (err) {
            ctx.body = {
                code: 102,
                data: null,
                msg: ''
            }
        }
    } else {
        ctx.body = {
            code: 102,
            data: null,
            msg: ''
        }
    }
})

router.post('/query', async(ctx) => {
    const { val, pageNum, pageSize } = ctx.request.body
    if (val) {
        const data = await sequelize.query(
            `select SQL_CALC_FOUND_ROWS Organ, VirusName, VirusTaxonomy 
             from OrganVirusData where 
             LOWER(Organ) LIKE '%${val}%' OR 
             LOWER(VirusName) LIKE '%${val}%' OR 
             LOWER(Family) LIKE '%${val}%' 
             limit ${(pageNum - 1) * pageSize}, ${pageSize}`,
            { type: QueryTypes.SELECT }
        );
        const total = await sequelize.query(
            `select FOUND_ROWS() as total`,
            { type: QueryTypes.SELECT }
        );
        ctx.body = {
            code: 101,
            data: {
                list: data,
                total: total[0].total
            },
            msg: '成功'
        }
    } else {
        ctx.body = {
            code: 102,
            data: null,
            msg: ''
        }
    }
})

module.exports = router
